#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Description: 用于奥凯软件检查导入的数据格式是否真确
@Project    ：ImportDataCheck
@File       ：checkdata.py
@IDE        ：PyCharm
@Author     ：YLF
@Email      ：76332706@163.com
@Date       ：2020/12/22 17:34
"""

import xlrd
import xlwt

import config


class CheckData:
    def __init__(self, file: str):
        """

        :param file: 需要检查的文件
        """
        # 实例化一个配置
        self.c = config.Config()
        # 需要检查的文件
        self.file = file
        # workbook
        self.workbook = None
        # sheet
        self.sheet = None
        # sheet row_count
        self.row_count = None
        # 检查完的数据写入一个新的文档
        self.write_xls_book, self.write_xls_sheet = self._create_write_xls()

        # error_style 如果信息有错误，则在写文件的时候增加一个style
        self.error_style = self._create_error_style()
        # waring 如果系统自动修正了数据，则在写文件的时候增加一个style
        self.waring_style = self._create_waring_style()
        # 正常style
        self.normal_style = self._create_normal_style()
        # 致命错误
        self.fatalError_style = self._create_fatal_error_style()
        # column
        self.column_count = None
        # 行错误提示信息
        self._row_err_info = []

    @staticmethod
    def _create_error_style():
        """
        创建错误数据的单元格填充格式
        :return:
        """
        font = xlwt.Font()
        font.colour_index = 2
        pattern = xlwt.Pattern()
        # 填充风格 May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        # 填充颜色 May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
        # 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown),
        # 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
        pattern.pattern_fore_colour = 5
        # style
        style = xlwt.XFStyle()  # Create the Pattern
        style.pattern = pattern
        style.font = font
        return style

    def _create_write_xls(self):
        self.write_xls_book = xlwt.Workbook(encoding='utf-8')
        self.write_xls_sheet = self.write_xls_book.add_sheet("sheet1")
        return self.write_xls_book, self.write_xls_sheet

    def _write_xls(self, data: tuple):
        """
        把数据写入单元格
        :param data: (row:int,col:int,data:str,if_error:int[0:right,1:wrong]),例如： (0,1,"aaa",0)
        :return:
        """
        self.write_xls_sheet.write(int(data[0]), int(data[1]), str(data[2]), data[3])

    def _save_file(self):
        # 拆分读入的Excel文件名
        _rl = self.file.split(".")
        _save_filename = ".".join(_rl[:-1]) + "_check." + _rl[-1]
        try:
            self.write_xls_book.save(_save_filename)
            return 0, 'ok'
        except Exception as e:
            _msg = "文件保存失败.\n{}".format(e)
            return 1, _msg

    def read_file(self, ):
        # 创建写入文件
        self._create_write_xls()
        # 打开需要读取的文件
        self.workbook = xlrd.open_workbook(self.file, )
        _sheet_names = self.workbook.sheet_names()
        self.sheet = self.workbook.sheet_by_index(0)
        self.row_count = self.sheet.nrows
        self.column_count = self.sheet.ncols
        if self.column_count > self.c.column_count:
            return 1, "文件{}格式错误，列数超过{}".format(self.file, self.c.column_count)
        return 0, 'ok'
        # 打印获取到的sheetname
        # print("文件{}包含有一下的sheet:{}\n一共有{}行数据".format(self.file, _sheet_names, self.row_count - 1))

    def check(self, ):
        for _index in range(0, self.row_count):
            self._row_err_info = []
            _row_data = self.sheet.row_values(_index)
            # 写表头
            if _index == 0:
                for _col in range(self.c.column_count):
                    _wt = (_index, _col, _row_data[_col], self.normal_style)
                    self._write_xls(_wt)
                _wt = (_index, 18, u"提示", self.normal_style)
                self._write_xls(_wt)
                continue
            # 错误信息记录
            # 第一列 条码
            _data, _style = self._check_barcode(str(_row_data[0]))
            _w = (_index, 0, _data, _style)
            self._write_xls(_w)

            # 第二列 品名
            _data, _style = self._check_name(_row_data[1])
            _w = (_index, 1, _data, _style)
            self._write_xls(_w)

            # 第三列 分类
            _data, _style = self._check_sort(_row_data[2])
            _w = (_index, 2, _data, _style)
            self._write_xls(_w)

            # 第四列 品牌
            _data, _style = self._check_brand(_row_data[3])
            _w = (_index, 3, _data, _style)
            self._write_xls(_w)
            # 第五列 零售价
            _data, _style = self._check_sellprice(_row_data[4], )
            _w = (_index, 4, _data, _style)
            self._write_xls(_w)

            # 第六列 供应商
            _data, _style = self._check_supplier(_row_data[5])
            _w = (_index, 5, _data, _style)
            self._write_xls(_w)

            # 第七列 自编码
            _data, _style = self._check_subcode(_row_data[6])
            _w = (_index, 6, _data, _style)
            self._write_xls(_w)

            # 第八列 商品简称
            _data, _style = self._check_subname(_row_data[7])
            _w = (_index, 7, _data, _style)
            self._write_xls(_w)

            # 第九列 单位
            _data, _style = self._check_unit(_row_data[8])
            _w = (_index, 8, _data, _style)
            self._write_xls(_w)

            # 第十列 规格
            _data, _style = self._check_spec(_row_data[9])
            _w = (_index, 9, _data, _style)
            self._write_xls(_w)

            # 第十一列 场地
            _data, _style = self._check_proceudre(_row_data[10])
            _w = (_index, 10, _data, _style)
            self._write_xls(_w)

            # 第十二列 进价
            _data, _style = self._check_costprice(_row_data[11], _row_data[4])
            _w = (_index, 11, _data, _style)
            self._write_xls(_w)

            # 第十三列 会员价
            _data, _style = self._check_viprice(_row_data[12])
            _w = (_index, 12, _data, _style)
            self._write_xls(_w)

            # 第十四列 批发价
            _data, _style = self._check_pfprice(_row_data[13])
            _w = (_index, 13, _data, _style)
            self._write_xls(_w)

            # 第十五列 称重标识
            _weight_flag = str(_row_data[14])
            _data, _style = self._check_weight_flag(_weight_flag)
            _w = (_index, 14, _data, _style)
            self._write_xls(_w)

            # 第十六列 联营扣率-0
            _lykl = _row_data[15]
            _data, _style = self._check_lykl(_lykl)
            _w = (_index, 15, _data, _style)
            self._write_xls(_w)

            # 第十七列 进货规格-1
            _data, _style = self._check_import_spec(_row_data[16])
            _w = (_index, 16, _data, _style)
            self._write_xls(_w)

            # 第十八列 商品类型
            _type = _row_data
            _data, _style = self._check_type(_type)
            _w = (_index, 17, _data, _style)
            self._write_xls(_w)

            # 最后一列写入错误提示信息
            _w = (_index, 18, u"。".join(self._row_err_info), self.normal_style)
            self._write_xls(_w)
        return self._save_file()

    def _check_barcode(self, data: str):
        """
        检查长度是否正确
        :return:
        """
        try:
            int(data)
        except:
            self._row_err_info.append("条码存在非数字字符！")
            return data, self.fatalError_style

        _len = len(data)
        if _len not in self.c.barcode_lenght:
            self._row_err_info.append(u"条码长度错误")
            return data, self.error_style
        else:
            return data, self.normal_style

    def _check_name(self, data: str):
        """
        检查品名是否有是空值
        :param data:
        :return:
        """
        if len(data) == 0 or len(data) > self.c.item_name_lenght:
            self._row_err_info.append(u"商品名称不存在，或长度超过40个字符")
            return data, self.error_style
        else:
            return data, self.normal_style

    def _check_sort(self, data: str):
        """
        检查分类长度是否正确
        :param data:
        :return: 0 正确，1 错误
        """
        try:
            if len(str(int(data))) / 2 != self.c.sort_level:
                self._row_err_info.append("类别编码长度错误,系统设定检查的长度是:{}".format(self.c.sort_level))
                return data, self.error_style
            else:
                return str(int(data)), self.normal_style
        except Exception as e:
            self._row_err_info.append("类别编码含有非法字符，请查证\n{}".format(e))
            return data, self.fatalError_style

    def _check_brand(self, data: str):
        """
        品牌检查，如果没有则用默认值PP填写
        :param data:
        :return:
        """
        if len(data) == 0:
            self._row_err_info.append("品牌为空，系统使用默认值，自动填写")
            return "PP", self.waring_style
        else:
            return data, self.normal_style

    def _check_sellprice(self, data, ):
        """
        检查售价
        :param data:
        :return:
        """
        try:
            if float(data) == 0.00:
                self._row_err_info.append("零售为0")
                return float(data), self.error_style
            else:
                return float(data), self.normal_style
        except:
            self._row_err_info.append("售价格式错误")
            return data, self.fatalError_style

    def _check_supplier(self, data: str):
        """
        检查分类长度是否正确
        :param data:
        :return: 0 正确，1 错误
        """
        if len(data) == 0:
            data = str(data)
            self._row_err_info.append("供应商为空")
            return data, self.error_style
        else:
            return data, self.normal_style

    def _check_subcode(self, data):
        """
        自编码检查
        :param data:
        :return:
        """
        data = str(data)
        if len(data) < 6:
            self._row_err_info.append("自编码长度太短，长度不能小于6")
            return data, self.error_style
        else:
            return data, self.normal_style

    def _check_subname(self, data):
        """
        商品简称检查
        :param data:
        :return:
        """
        if len(data) == 0:
            self._row_err_info.append("单位不能为空")
            return data, self.fatalError_style
        else:
            return data, self.normal_style

    def _check_unit(self, data):
        """
        单位检查
        :param data:
        :return:
        """
        if len(data) == 0:
            self._row_err_info.append("单位不能为空")
            return data, self.error_style
        else:
            return data, self.normal_style

    @staticmethod
    def _create_waring_style():
        pattern = xlwt.Pattern()
        # 填充风格 May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        # 填充颜色 May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
        # 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown),
        # 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
        pattern.pattern_fore_colour = 17
        # style
        style = xlwt.XFStyle()  # Create the Pattern
        style.pattern = pattern
        return style

    @staticmethod
    def _create_normal_style():
        font = xlwt.Font()
        style = xlwt.XFStyle()
        style.font = font
        return style

    @staticmethod
    def _create_fatal_error_style():
        pattern = xlwt.Pattern()
        # 填充风格 May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        # 填充颜色 May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
        # 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown),
        # 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
        pattern.pattern_fore_colour = 2
        # style
        style = xlwt.XFStyle()  # Create the Pattern
        style.pattern = pattern
        return style

    def _check_costprice(self, data, sell_price):
        """
        检查售价，同时检查售价是否小于进价
        :param data:
        :param sell_price:
        :return:
        """
        try:
            if float(data) > float(sell_price):
                self._row_err_info.append(u"售价小于进价")
            if float(data) == 0.0:
                self._row_err_info.append(u"进价等于0")
                return float(data), self.error_style
            return float(data), self.normal_style
        except:
            self._row_err_info.append("进价错误")
            return data, self.fatalError_style

    def _check_weight_flag(self, data):
        try:
            data = str(int(data))
            if len(data) == 0:
                self._row_err_info.append("称重标识为空，使用系统默认值填写")
                return "0", self.waring_style
            if int(data) != 0:
                self._row_err_info.append("称重标识只能为0")
                return data, self.error_style
            return data, self.normal_style
        except:
            self._row_err_info.append("称重标识只能是数字")
            return data, self.fatalError_style

    def _check_type(self, data):
        try:
            if int(data) == 0:
                self._row_err_info.append("商品类型为空，使用系统默认值填写")
                return "0", self.waring_style
            if int(data) != 0:
                self._row_err_info.append("商品类型只能为0")
                return data, self.error_style
            return data, self.normal_style
        except:
            self._row_err_info.append("商品类型只能是数字")
            return data, self.fatalError_style

    def _check_lykl(self, data):
        """
        联营扣率检查
        :param data:
        :return:
        """
        try:
            if float(data) == 0.0:
                self._row_err_info.append("联营扣率为空，使用系统默认值填写")
                return "0", self.waring_style
            if float(data) < 0 or float(data) > 1:
                self._row_err_info.append("联营扣率只能在0和1之间,25的扣点，请填写0.25")
                return data, self.error_style
            return data, self.normal_style
        except:
            self._row_err_info.append("联营扣率只能是数字")
            return data, self.fatalError_style

    def _check_import_spec(self, data):
        try:
            if data == '':
                self._row_err_info.append("进货规格为空，使用系统默认值填写")
                return "1", self.waring_style
            if int(data) == 0:
                self._row_err_info.append("进货规格最少是1")
                return data, self.error_style
            return data, self.normal_style
        except:
            self._row_err_info.append("进货规格只能是数字")
            return data, self.fatalError_style

    def _check_pfprice(self, data):
        """
        批发价
        :param data:
        :return:
        """
        try:
            if float(data) < 0:
                self._row_err_info.append("批发价不能小于0")
                return float(data), self.error_style
            return float(data), self.normal_style
        except:
            self._row_err_info.append("批发价只能是数字")
            return data, self.fatalError_style

    def _check_viprice(self, data):
        """
        批发价
        :param data:
        :return:
        """
        try:
            if float(data) < 0:
                self._row_err_info.append("会员价不能小于0")
                return float(data), self.error_style
            return float(data), self.normal_style
        except:
            self._row_err_info.append("会员价只能是数字")
            return data, self.fatalError_style

    def _check_spec(self, data):
        return data, self.normal_style

    def _check_proceudre(self, data):
        return data, self.normal_style

    def set_sort_level(self, data: int):
        self.c.set_sort_level(data)

    def __str__(self):
        return "\n".join(["{}:{}".format(k, v) for k, v in self.__dict__.items()])


if __name__ == '__main__':
    cd = CheckData(r'C:\Users\YLF\Desktop\bb.xls')
    k, v = cd.read_file()
    if k != 0:
        print(v)
    else:
        print(cd)
        cd.check()
